﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Agresso.Interface.Layer.SQL
{
    public class FreightCommisionInvoiceSQL
    {
        public static string GetAllFreightCommisionInvoice()
        {
            string sql =@"Select 
	                    vProfitCentreOrganisationSrch.ProfitCentreName Profit_Centre_Fullname,
	                    vInvoiceSrch.Type Type,
	                    vVMVoyageSrch.VesselName Vessel_Name,
	                    vVMVoyageSrch.VoyageNo Voyage_No,
	                    vInvoiceSrch.Curr Currency,
	                    vOpInvoiceSrch.InvChargeAmount Charge_Amount,
	                    vOpInvoiceSrch.BuyerFullName Charge_Charterer,
	                    vOpInvoiceSrch.Narrative Charge_Description,
	                    vInvoiceSrch.DocInvDate Date,	
	                    vInvoiceSrch.DueDate Due_Date,
	                    vOpInvoiceSrch.ChargeType Charge_Type,
	                    vInvoiceSrch.InvoiceNo Number,
	                    vInvoiceSrch.Paid Paid
	                    --Cast(vInvoiceSrch.DocInvDate as float)+2 Calc_DocInvDate,
	                    --Cast(vInvoiceSrch.DueDate as float)+2 Calc_DueDate
                    FROM VVMVOYAGESRCH 
                    JOIN VINVOICESRCH ON VVMVOYAGESRCH.ID = VINVOICESRCH.VOYAGE 
                    JOIN VPROFITCENTREORGANISATIONSRCH ON VVMVOYAGESRCH.ID = VPROFITCENTREORGANISATIONSRCH.ID 
                    JOIN VDEPTORGANISATIONSRCH ON VVMVOYAGESRCH.ID=VDEPTORGANISATIONSRCH.ID 
                    JOIN DELTAVIEWPERMSETTING ON VVMVOYAGESRCH.COMPANY=DELTAVIEWPERMSETTING.DEPT 
                    LEFT OUTER JOIN VOPINVOICESRCH ON VVMVOYAGESRCH.ID=VOPINVOICESRCH.VOY AND 
	                    VINVOICESRCH.FIX=VOPINVOICESRCH.FIX AND 
	                    VINVOICESRCH.OPNO=VOPINVOICESRCH.OPNO AND 
	                    VINVOICESRCH.VYNO=VOPINVOICESRCH.VYNO AND 
	                    VINVOICESRCH.GRNO=VOPINVOICESRCH.GRNO AND 
	                    VINVOICESRCH.NO=VOPINVOICESRCH.NO 
                    LEFT OUTER JOIN VVMOPFIXSRCH ON VINVOICESRCH.FIX = VVMOPFIXSRCH.OPSFIXTURE AND 
	                    VVMVOYAGESRCH.ID = VVMOPFIXSRCH.VMVOYAGENO 
                    CROSS JOIN VSSLCLIENTCONFIG 
                    WHERE vVMVoyageSrch.Category NOT LIKE 'UnFixed' AND 
                    (vVMVoyageSrch.VesselName is NOT NULL) AND 
                    (vInvoiceSrch.InvoiceNo <> 'proforma') AND 
                    (vInvoiceSrch.Paid = 0) AND 
                    DeltaviewPermSetting.ViewPerm=20 AND 
                    DeltaviewPermSetting.Person= 11
                    ORDER BY vVMVoyageSrch.VesselName, vVMVoyageSrch.VoyageNo
                    ";
            return sql;
        }
        public static string GetAgressoCustomerCount()
        {
            string sql = "select count(*) from acuheader where apar_name = @apar_name";
            return sql;
        }
        public static string GetAgressoSupplierCount()
        {
            string sql = "select count(*) from asuheader where apar_name = @apar_name";
            return sql;
        }
        public static string GetMiddleLayerCompanyByName()
        {
            string sql = "select count(*) from asuheader where apar_name = @department";
            return sql;
        }
    }
}
